insert overwrite table jms_dm.dm_duration_of_whole_process_pre_dt partition(dt)
select
      wide.sign_date           --揽收日期
     ,wide.send_network_code   --始发网点编码
     ,wide.send_network_name   --始发网点名称
     ,wide.sign_network_code   --签收网点编码
     ,wide.sign_network_name   --签收网点名称
     ,wide.send_area_id        --始发区县id
     ,wide.send_area_desc      --始发区县名称
     ,wide.send_city_id        --始发城市id
     ,wide.send_city_desc      --始发城市名称
     ,wide.send_agent_code     --始发代理区code
     ,wide.send_agent_name     --始发代理区名称
     ,wide.send_fran_code      --始发加盟商code
     ,wide.send_fran_name      --始发加盟商名称
     ,wide.send_provider_id    --始发省份id
     ,wide.send_provider_desc  --始发省份名称
     ,wide.send_regional_id    --始发大区id
     ,wide.send_regional_desc  --始发大区名称
     ,wide.sign_area_id        --签收区县id
     ,wide.sign_area_desc      --签收区县名称
     ,wide.sign_city_id        --签收城市id
     ,wide.sign_city_desc      --签收城市名称
     ,wide.sign_agent_code     --签收代理区code
     ,wide.sign_agent_name     --签收代理区名称
     ,wide.sign_fran_code      --签收加盟商code
     ,wide.sign_fran_name      --签收加盟商名称
     ,wide.sign_provider_id    --签收省份id
     ,wide.sign_provider_desc  --签收省份名称
     ,wide.sign_regional_id    --签收大区id
     ,wide.sign_regional_desc  --签收大区名称
     ,0 as order_source_id     --订单来源id
     ,wide.ordersource_code    --订单来源编码
     ,wide.ordersource_name    --订单来源名称
     ,wide.deliver_user_code as deliver_later_user_code  --出仓员编码
     ,wide.deliver_user_name as deliver_later_user       --出仓员名称
     ,round(sum(wide.pre_sign_taking_difftime             ),2) as pre_sign_taking_time
     ,round(sum(wide.real_sign_taking_difftime            ),2) as real_sign_taking_time
     ,round(sum(wide.first_center_taking_difftime         ),2) as first_center_taking_time
     ,round(sum(wide.inout_first_center_difftime          ),2) as inout_first_center_time
     ,round(sum(wide.dest_first_center_difftime           ),2) as dest_first_center_time
     ,round(sum(wide.last_first_center_difftime           ),2) as last_first_center_time
     ,round(sum(wide.inout_last_center_difftime           ),2) as inout_last_center_time
     ,round(sum(wide.dest_last_center_difftime            ),2) as dest_last_center_time
     ,round(sum(wide.pre_sign_dest_difftime               ),2) as pre_sign_dest_time
     ,round(sum(wide.real_pre_sign_difftime               ),2) as real_pre_sign_time
     ,round(sum(wide.network_taking_send_difftime         ),2) as network_taking_send_time
     ,round(sum(wide.network_send_nodal_arrival_difftime  ),2) as network_send_nodal_arrival_time
     ,round(sum(wide.first_nodal_arrival_send_difftime    ),2) as first_nodal_arrival_send_time
     ,round(sum(wide.nodal_send_center_arrival_difftime   ),2) as nodal_send_center_arrival_time
     ,round(sum(wide.nodal_arrival_center_send_difftime   ),2) as nodal_arrival_center_send_time
     ,round(sum(wide.end_nodal_arrival_send_difftime      ),2) as end_nodal_arrival_send_time
     ,round(sum(wide.nodal_send_network_arrival_difftime  ),2) as nodal_send_network_arrival_time
     ,round(sum(wide.end_network_arrival_deliver_difftime ),2) as end_network_arrival_deliver_time
     ,round(sum(wide.deliver_aging_sign_difftime          ),2) as deliver_aging_sign_time
     ,round(sum(wide.actual_aging_sign_difftime           ),2) as actual_aging_sign_time
     ,sum(case when wide.pre_sign_taking_difftime            >0 then 1 else 0 end) as pre_sign_taking_cnt
     ,sum(case when wide.real_sign_taking_difftime           >0 then 1 else 0 end) as real_sign_taking_cnt
     ,sum(case when wide.first_center_taking_difftime        >0 then 1 else 0 end) as first_center_taking_cnt
     ,sum(case when wide.inout_first_center_difftime         >0 then 1 else 0 end) as inout_first_center_cnt
     ,sum(case when wide.dest_first_center_difftime          >0 then 1 else 0 end) as dest_first_center_cnt
     ,sum(case when wide.last_first_center_difftime          >0 then 1 else 0 end) as last_first_center_cnt
     ,sum(case when wide.inout_last_center_difftime          >0 then 1 else 0 end) as inout_last_center_cnt
     ,sum(case when wide.dest_last_center_difftime           >0 then 1 else 0 end) as dest_last_center_cnt
     ,sum(case when wide.pre_sign_dest_difftime              >0 then 1 else 0 end) as pre_sign_dest_cnt
     ,sum(case when wide.real_pre_sign_difftime              >0 then 1 else 0 end) as real_pre_sign_cnt
     ,sum(case when wide.network_taking_send_difftime        >0 then 1 else 0 end) as network_taking_send_cnt
     ,sum(case when wide.network_send_nodal_arrival_difftime >0 then 1 else 0 end) as network_send_nodal_arrival_cnt
     ,sum(case when wide.first_nodal_arrival_send_difftime   >0 then 1 else 0 end) as first_nodal_arrival_send_cnt
     ,sum(case when wide.nodal_send_center_arrival_difftime  >0 then 1 else 0 end) as nodal_send_center_arrival_cnt
     ,sum(case when wide.nodal_arrival_center_send_difftime  >0 then 1 else 0 end) as nodal_arrival_center_send_cnt
     ,sum(case when wide.end_nodal_arrival_send_difftime     >0 then 1 else 0 end) as end_nodal_arrival_send_cnt
     ,sum(case when wide.nodal_send_network_arrival_difftime >0 then 1 else 0 end) as nodal_send_network_arrival_cnt
     ,sum(case when wide.end_network_arrival_deliver_difftime>0 then 1 else 0 end) as end_network_arrival_deliver_cnt
     ,sum(case when wide.deliver_aging_sign_difftime         >0 then 1 else 0 end) as deliver_aging_sign_cnt
     ,sum(case when wide.actual_aging_sign_difftime          >0 then 1 else 0 end) as actual_aging_sign_cnt
     ,sum(case when wide.deliver_aging_sign_difftime > 24 then 1 else 0 end) as if_deliver_24h_cnt  --派件-时效签收超24H量
     ,wide.sign_date as dt --签收日期
from jms_dws.dws_wide_unsign_whole_effect_dt wide
where wide.dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
  and wide.send_network_code is not null
  and wide.sign_date between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
  and wide.if_reback    <> 1
  and wide.if_end_piece <> 1
  and wide.if_intercept <> 1
group by wide.sign_date           --揽收日期
        ,wide.send_network_code   --始发网点编码
        ,wide.send_network_name   --始发网点名称
        ,wide.sign_network_code   --签收网点编码
        ,wide.sign_network_name   --签收网点名称
        ,wide.send_area_id        --始发区县id
        ,wide.send_area_desc      --始发区县名称
        ,wide.send_city_id        --始发城市id
        ,wide.send_city_desc      --始发城市名称
        ,wide.send_agent_code     --始发代理区code
        ,wide.send_agent_name     --始发代理区名称
        ,wide.send_fran_code      --始发加盟商code
        ,wide.send_fran_name      --始发加盟商名称
        ,wide.send_provider_id    --始发省份id
        ,wide.send_provider_desc  --始发省份名称
        ,wide.send_regional_id    --始发大区id
        ,wide.send_regional_desc  --始发大区名称
        ,wide.sign_area_id        --签收区县id
        ,wide.sign_area_desc      --签收区县名称
        ,wide.sign_city_id        --签收城市id
        ,wide.sign_city_desc      --签收城市名称
        ,wide.sign_agent_code     --签收代理区code
        ,wide.sign_agent_name     --签收代理区名称
        ,wide.sign_fran_code      --签收加盟商code
        ,wide.sign_fran_name      --签收加盟商名称
        ,wide.sign_provider_id    --签收省份id
        ,wide.sign_provider_desc  --签收省份名称
        ,wide.sign_regional_id    --签收大区id
        ,wide.sign_regional_desc  --签收大区名称
        ,wide.ordersource_code    --订单来源编码
        ,wide.ordersource_name    --订单来源名称
        ,wide.deliver_user_code   --出仓员编码
        ,wide.deliver_user_name   --出仓员名称
distribute by wide.sign_date,pmod(hash(rand()),30)
;